package gov.nhso.prov.acc.apps.acc6;

import java.io.OutputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;

@SuppressWarnings({"rawtypes","deprecation","unchecked"})
public class ACC60402 extends Utility{
	public String url = "/apps/acc6/ACC60402A.jsp";

	public ACC60402(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.init(request);
		}else if (job.equals("search")){
			this.search(request);
		}else if (job.equals("export")){
			this.export(request, response);
		}
	}
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			//request.setAttribute("lsAllocate", this.dataUtil(dao).lsAllocateLvl1());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			String allocateCode = this.getAttr(request, "allocateCode");
			String allocateCodeLvl2 = this.getAttr(request, "allocateCodeLvl2");
			List listData = new ArrayList();
			List listAllocate = new ArrayList();
			Map allocateMap = new HashMap();
			StringBuffer sb = new StringBuffer(); 
			String provTxt = "";

			if (provinceId.equals("")) {
				if (user(request).getAreaLevel().equals("2")) {
					provTxt = " IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+user(request).getAreaCode()+"') ";
				} else {
					provTxt = " IS NOT NULL";
				}
			} else {
				provTxt = " = '"+provinceId+"' ";
			}
			
			if (allocateCodeLvl2.equals("")){
				sb.append("SELECT ALLOCATE_CODE,ALLOCATE_NAME,ALLOCATE_LEVEL,ALLOCATE_SEQ,ALLOCATE_APPLY ");
				sb.append("FROM ACC6_ALLOCATE ");
				sb.append("WHERE ALLOCATE_CODE LIKE '"+allocateCode+"%' AND ALLOCATE_LEVEL IS NOT NULL ORDER BY ALLOCATE_SEQ ");
			} else {
				sb.append("SELECT *  ");
				sb.append("FROM ( ");
				sb.append("SELECT ALLOCATE_CODE,ALLOCATE_NAME,ALLOCATE_LEVEL,ALLOCATE_SEQ,ALLOCATE_APPLY ");
				sb.append("FROM ACC6_ALLOCATE  ");	
				sb.append("WHERE ALLOCATE_CODE = '"+allocateCode+"' AND ALLOCATE_LEVEL IS NOT NULL ");	
				sb.append("UNION ");
				sb.append("SELECT ALLOCATE_CODE,ALLOCATE_NAME,ALLOCATE_LEVEL,ALLOCATE_SEQ,ALLOCATE_APPLY ");
				sb.append("FROM ACC6_ALLOCATE  ");
				sb.append("WHERE PARENT_CODE = '"+allocateCodeLvl2+"' OR ALLOCATE_CODE = '"+allocateCodeLvl2+"' ");
				sb.append("AND ALLOCATE_LEVEL IS NOT NULL) A ORDER BY  ALLOCATE_SEQ ");
			}
			listAllocate = dao.listDataSql(sb.toString());
			for (int i = 0;i<listAllocate.size();i++){
				allocateMap = (Map) listAllocate.get(i);
				Map result = new HashMap();
				Map bufferMap = new HashMap();
				sb.delete(0, sb.length());
				if(allocateMap.get("ALLOCATE_APPLY").toString().equals("N")){
					sb.delete(0, sb.length());
					sb.append("SELECT '" + allocateMap.get("ALLOCATE_CODE").toString() + "' AS AC, NVL(SUM(I),0) AS I, NVL(SUM(X),0) AS X,NVL(SUM(I),0) - NVL(SUM(X),0) AS R ");
					sb.append("FROM ( ");
					sb.append("SELECT ");
					sb.append("CASE WHEN TRANSACTION_TYPE  NOT IN ('T', 'C', 'A', 'P') THEN SUM(NVL(INCOME_AMOUNT,0)) ELSE 0 END AS I, ");
					sb.append("CASE WHEN TRANSACTION_TYPE  IN ('T', 'C', 'A', 'P') THEN SUM(NVL(EXPENSE_AMOUNT,0)) ELSE 0 END AS X ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if (allocateMap.get("ALLOCATE_LEVEL").toString().equals("1")) {
						sb.append("AND ALLOCATE_CODE LIKE '"+allocateMap.get("ALLOCATE_CODE").toString()+"%' ");
					} else {
						sb.append("AND ALLOCATE_CODE LIKE '"+allocateMap.get("ALLOCATE_CODE").toString().substring(0, 6)+"%' ");
					}
					sb.append("AND REASON_CODE = '00' ");
					sb.append(" GROUP BY TRANSACTION_TYPE  ) ");
					
					bufferMap = dao.findDataSql(sb.toString());
					result.put("code", allocateMap.get("ALLOCATE_CODE").toString());
					result.put("name", allocateMap.get("ALLOCATE_NAME").toString());
					result.put("level", allocateMap.get("ALLOCATE_LEVEL").toString());
					result.put("apply", allocateMap.get("ALLOCATE_APPLY").toString());
					result.put("I", bufferMap.get("I"));
					result.put("X", bufferMap.get("X"));
					result.put("R", bufferMap.get("R"));
					result.put("date", "");
					listData.add(result);
				} else if(allocateMap.get("ALLOCATE_APPLY").toString().equals("Y")){
					sb.append("SELECT '"+allocateMap.get("ALLOCATE_CODE").toString()+"' AS AC, NVL(SUM(I),0) AS I, NVL(SUM(X),0) AS X,NVL(SUM(I),0) - NVL(SUM(X),0) AS R  ");
					sb.append("FROM ( ");
					sb.append("SELECT ");
					sb.append("CASE WHEN TRANSACTION_TYPE  NOT IN ('T', 'C', 'A', 'P') THEN SUM(NVL(INCOME_AMOUNT,0)) ELSE 0 END AS I, ");
					sb.append("CASE WHEN TRANSACTION_TYPE  IN ('T', 'C', 'A', 'P') THEN SUM(NVL(EXPENSE_AMOUNT,0)) ELSE 0 END AS X ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" AND ALLOCATE_CODE = '"+allocateMap.get("ALLOCATE_CODE").toString()+"' AND REASON_CODE = '00' GROUP BY TRANSACTION_TYPE  ) ");
					bufferMap = dao.findDataSql(sb.toString());
					result.put("code", allocateMap.get("ALLOCATE_CODE").toString());
					result.put("name", allocateMap.get("ALLOCATE_NAME").toString());
					result.put("level", allocateMap.get("ALLOCATE_LEVEL").toString());
					result.put("apply", allocateMap.get("ALLOCATE_APPLY").toString());
					result.put("I", bufferMap.get("I"));
					result.put("X", bufferMap.get("X"));
					result.put("R", bufferMap.get("R"));
					result.put("date", "");
					listData.add(result);

					sb.delete(0, sb.length());
					sb.append("SELECT ALLOCATE_CODE, TRANSACTION_TYPE, TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP, REF_NO, ");
					sb.append("CASE WHEN TRANSACTION_TYPE  NOT IN ('T', 'C', 'A', 'P') THEN NVL(INCOME_AMOUNT,0) ELSE 0 END AS I, ");
					sb.append("CASE WHEN TRANSACTION_TYPE  IN ('T', 'C', 'A', 'P') THEN NVL(EXPENSE_AMOUNT,0) ELSE 0 END AS X, ");
					sb.append("CASE WHEN INCOME_DATE IS NULL THEN EXPENSE_DATE ELSE INCOME_DATE END AS DATES ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" AND ALLOCATE_CODE = '"+allocateMap.get("ALLOCATE_CODE").toString()+"' ");
					sb.append("AND ( INCOME_AMOUNT <> 0 OR  EXPENSE_AMOUNT <> 0) ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append("ORDER BY INCOME_DATE ASC,EXPENSE_DATE ASC ");
					List ls = dao.listDataSql(sb.toString());
					for (int x = 0; x < ls.size(); x++) {
						result = new HashMap();
						bufferMap = (Map) ls.get(x);
						sb.delete(0, sb.length());
						if (bufferMap.get("TRANSACTION_TYPE").toString().equals("T") || bufferMap.get("TRANSACTION_TYPE").toString().equals("A")){
							sb.append("SELECT DOC_NO, TRANSFER_DESC AS DESCS ");
							sb.append("FROM " + this.schema + ".ACC6_EXPENSE_TRANSFER ");
							sb.append("WHERE  TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " ");
						} else if (bufferMap.get("TRANSACTION_TYPE").toString().equals("C")){
							sb.append("SELECT DOC_NO, CHEQUE_DESC AS DESCS ");
							sb.append("FROM "+ this.schema + ".ACC6_EXPENSE_CHEQUE ");
							sb.append("WHERE TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " ");
						} else if (bufferMap.get("TRANSACTION_TYPE").toString().equals("P")){
							sb.append("SELECT DOC_NO, EXPENSE_DESC AS DESCS ");
							sb.append("FROM "+ this.schema + ".ACC6_EXPENSE_OTHER ");
							sb.append("WHERE TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " ");
						} else if (bufferMap.get("TRANSACTION_TYPE").toString().equals("F") || bufferMap.get("TRANSACTION_TYPE").toString().equals("O") || bufferMap.get("TRANSACTION_TYPE").toString().equals("R")){
							sb.append("SELECT DOC_NO, INCOME_DESC AS DESCS ");
							sb.append("FROM " + this.schema + ".ACC6_INCOME ");
							sb.append("WHERE PROVINCE_ID "+provTxt+" ");
							sb.append("AND TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " ");
						} else if (bufferMap.get("TRANSACTION_TYPE").toString().equals("B")) {
							sb.append("SELECT '' AS DOC_NO, 'ยอดยกมา' AS DESCS ");
							sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
							sb.append("WHERE PROVINCE_ID "+provTxt+" ");
							sb.append("AND TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " AND INCOME_AMOUNT <> 0 ");
						} else if (bufferMap.get("TRANSACTION_TYPE").toString().equals("S")) {
							sb.append("SELECT '' AS DOC_NO, 'จาก SAP' AS DESCS ");
							sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
							sb.append("WHERE PROVINCE_ID "+provTxt+" ");
							sb.append("AND TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " ");
						}
						Map map = dao.findDataSql(sb.toString());

						result.put("code", allocateMap.get("ALLOCATE_CODE").toString());
						result.put("name", allocateMap.get("ALLOCATE_NAME").toString());
						result.put("level", allocateMap.get("ALLOCATE_LEVEL").toString());
						result.put("apply", allocateMap.get("ALLOCATE_APPLY").toString());
						result.put("I", bufferMap.get("I"));
						result.put("X", bufferMap.get("X"));
						result.put("R", "0");
						result.put("date", bufferMap.get("DATES"));
						result.put("refNo", bufferMap.get("REF_NO"));
						result.put("docNo", map.get("DOC_NO"));
						result.put("desc", map.get("DESCS"));
						listData.add(result);
					}
				}
				
			}
			
			if (listData.size() < 1) {
				this.message(request).setMsgCode("I001");
			}
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("allocateCode", allocateCode);
			request.setAttribute("allocateCodeLvl2", allocateCodeLvl2);
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("allocateName", this.dataUtil(dao).getAllocateById(allocateCode));
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("lsAllocateLvl1", this.dataUtil(dao).lsAllocateLvl1ByBudgetYear(budgetYear));
			request.setAttribute("lsAllocateLvl2", this.dataUtil(dao).lsAllocateLvl2ByBudgetYear(budgetYear,allocateCode));
			request.setAttribute("listData", listData);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private void export(HttpServletRequest request, HttpServletResponse response){
		
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			String allocateCode = this.getAttr(request, "allocateCode");
			String allocateCodeLvl2 = this.getAttr(request, "allocateCodeLvl2");
			List listData = new ArrayList();
			List listAllocate = new ArrayList();
			Map allocateMap = new HashMap();
			StringBuffer sb = new StringBuffer(); 
			String provTxt = "";

			if (provinceId.equals("")) {
				if (user(request).getAreaLevel().equals("2")) {
					provTxt = " IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+user(request).getAreaCode()+"') ";
				} else {
					provTxt = " IS NOT NULL";
				}
			} else {
				provTxt = " = '"+provinceId+"' ";
			}
			if (allocateCodeLvl2.equals("")){
				sb.append("SELECT ALLOCATE_CODE,ALLOCATE_NAME,ALLOCATE_LEVEL,ALLOCATE_SEQ,ALLOCATE_APPLY ");
				sb.append("FROM ACC6_ALLOCATE ");
				sb.append("WHERE ALLOCATE_CODE LIKE '"+allocateCode+"%' AND ALLOCATE_LEVEL IS NOT NULL ORDER BY ALLOCATE_SEQ ");
			} else {
				sb.append("SELECT *  ");
				sb.append("FROM ( ");
				sb.append("SELECT ALLOCATE_CODE,ALLOCATE_NAME,ALLOCATE_LEVEL,ALLOCATE_SEQ,ALLOCATE_APPLY ");
				sb.append("FROM ACC6_ALLOCATE  ");	
				sb.append("WHERE ALLOCATE_CODE = '"+allocateCode+"' AND ALLOCATE_LEVEL IS NOT NULL ");	
				sb.append("UNION ");
				sb.append("SELECT ALLOCATE_CODE,ALLOCATE_NAME,ALLOCATE_LEVEL,ALLOCATE_SEQ,ALLOCATE_APPLY ");
				sb.append("FROM ACC6_ALLOCATE  ");
				sb.append("WHERE PARENT_CODE = '"+allocateCodeLvl2+"' OR ALLOCATE_CODE = '"+allocateCodeLvl2+"' ");
				sb.append("AND ALLOCATE_LEVEL IS NOT NULL) A ORDER BY  ALLOCATE_SEQ ");
			}
			
			listAllocate = dao.listDataSql(sb.toString());
			for (int i = 0;i<listAllocate.size();i++){
				allocateMap = (Map) listAllocate.get(i);
				Map result = new HashMap();
				Map bufferMap = new HashMap();
				sb.delete(0, sb.length());
				if(allocateMap.get("ALLOCATE_APPLY").toString().equals("N")){	
					sb.delete(0, sb.length());
					sb.append("SELECT '" + allocateMap.get("ALLOCATE_CODE").toString() + "' AS AC, NVL(SUM(I),0) AS I, NVL(SUM(X),0) AS X,NVL(SUM(I),0) - NVL(SUM(X),0) AS R  ");
					sb.append("FROM ( ");
					sb.append("SELECT ");
					sb.append("CASE WHEN TRANSACTION_TYPE  NOT IN ('T', 'C', 'A', 'P') THEN SUM(INCOME_AMOUNT) ELSE 0 END AS I, ");
					sb.append("CASE WHEN TRANSACTION_TYPE  IN ('T', 'C', 'A', 'P') THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" ");
					if (allocateMap.get("ALLOCATE_LEVEL").toString().equals("1")) {
						sb.append("AND ALLOCATE_CODE LIKE '"+allocateMap.get("ALLOCATE_CODE").toString()+"%' ");
					} else {
						sb.append("AND ALLOCATE_CODE LIKE '"+allocateMap.get("ALLOCATE_CODE").toString().substring(0, 6)+"%' ");
					}
					sb.append("AND REASON_CODE = '00' ");
					sb.append(" GROUP BY TRANSACTION_TYPE  ) ");
					
					bufferMap = dao.findDataSql(sb.toString());
					result.put("code", allocateMap.get("ALLOCATE_CODE").toString());
					result.put("name", allocateMap.get("ALLOCATE_NAME").toString());
					result.put("level", allocateMap.get("ALLOCATE_LEVEL").toString());
					result.put("I", bufferMap.get("I"));
					result.put("X", bufferMap.get("X"));
					result.put("R", bufferMap.get("R"));
					result.put("date", "");
					listData.add(result);
				} else if(allocateMap.get("ALLOCATE_APPLY").toString().equals("Y")){	
					sb.append("SELECT '"+allocateMap.get("ALLOCATE_CODE").toString()+"' AS AC, NVL(SUM(I),0) AS I, NVL(SUM(X),0) AS X,NVL(SUM(I),0) - NVL(SUM(X),0) AS R  ");
					sb.append("FROM ( ");
					sb.append("SELECT ");
					sb.append("CASE WHEN TRANSACTION_TYPE  NOT IN ('T', 'C', 'A', 'P') THEN SUM(INCOME_AMOUNT) ELSE 0 END AS I, ");
					sb.append("CASE WHEN TRANSACTION_TYPE  IN ('T', 'C', 'A', 'P') THEN SUM(EXPENSE_AMOUNT) ELSE 0 END AS X ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" AND ALLOCATE_CODE = '"+allocateMap.get("ALLOCATE_CODE").toString()+"' AND REASON_CODE = '00' GROUP BY TRANSACTION_TYPE  ) ");
					bufferMap = dao.findDataSql(sb.toString());
					result.put("code", allocateMap.get("ALLOCATE_CODE").toString());
					result.put("name", allocateMap.get("ALLOCATE_NAME").toString());
					result.put("level", allocateMap.get("ALLOCATE_LEVEL").toString());
					result.put("I", bufferMap.get("I"));
					result.put("X", bufferMap.get("X"));
					result.put("R", bufferMap.get("R"));
					result.put("date", "");
					listData.add(result);

					sb.delete(0, sb.length());
					sb.append("SELECT ALLOCATE_CODE, TRANSACTION_TYPE, TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP, REF_NO, ");
					sb.append("CASE WHEN TRANSACTION_TYPE  NOT IN ('T', 'C', 'A', 'P') THEN INCOME_AMOUNT ELSE 0 END AS I, ");
					sb.append("CASE WHEN TRANSACTION_TYPE  IN ('T', 'C', 'A', 'P') THEN EXPENSE_AMOUNT ELSE 0 END AS X, ");
					sb.append("CASE WHEN INCOME_DATE IS NULL THEN EXPENSE_DATE ELSE INCOME_DATE END AS DATES ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID "+provTxt+" AND ALLOCATE_CODE = '"+allocateMap.get("ALLOCATE_CODE").toString()+"' ");
					sb.append("AND ( INCOME_AMOUNT <> 0 OR  EXPENSE_AMOUNT <> 0) ");
					sb.append("AND REASON_CODE = '00' ");
					sb.append("ORDER BY INCOME_DATE ASC,EXPENSE_DATE ASC ");
					List ls = dao.listDataSql(sb.toString());
					for (int x = 0; x < ls.size(); x++) {
						result = new HashMap();
						bufferMap = (Map) ls.get(x);
						sb.delete(0, sb.length());
						if (bufferMap.get("TRANSACTION_TYPE").toString().equals("T") || bufferMap.get("TRANSACTION_TYPE").toString().equals("A")){
							sb.append("SELECT DOC_NO, TRANSFER_DESC AS DESCS ");
							sb.append("FROM " + this.schema + ".ACC6_EXPENSE_TRANSFER ");
							sb.append("WHERE  TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " ");
						} else if (bufferMap.get("TRANSACTION_TYPE").toString().equals("C")){
							sb.append("SELECT DOC_NO, CHEQUE_DESC AS DESCS ");
							sb.append("FROM "+ this.schema + ".ACC6_EXPENSE_CHEQUE ");
							sb.append("WHERE TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " ");
						} else if (bufferMap.get("TRANSACTION_TYPE").toString().equals("P")){
							sb.append("SELECT DOC_NO, EXPENSE_DESC AS DESCS ");
							sb.append("FROM "+ this.schema + ".ACC6_EXPENSE_OTHER ");
							sb.append("WHERE TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " ");
						} else if (bufferMap.get("TRANSACTION_TYPE").toString().equals("F") || bufferMap.get("TRANSACTION_TYPE").toString().equals("O") || bufferMap.get("TRANSACTION_TYPE").toString().equals("R")){
							sb.append("SELECT DOC_NO, INCOME_DESC AS DESCS ");
							sb.append("FROM " + this.schema + ".ACC6_INCOME ");
							sb.append("WHERE PROVINCE_ID "+provTxt+" ");
							sb.append("AND TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " ");
						} else if (bufferMap.get("TRANSACTION_TYPE").toString().equals("B")) {
							sb.append("SELECT '' AS DOC_NO, 'ยอดยกมา' AS DESCS ");
							sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
							sb.append("WHERE PROVINCE_ID "+provTxt+" ");
							sb.append("AND TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + "  AND INCOME_AMOUNT <> 0 ");
						} else if (bufferMap.get("TRANSACTION_TYPE").toString().equals("S")) {
							sb.append("SELECT '' AS DOC_NO, 'จาก SAP' AS DESCS ");
							sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
							sb.append("WHERE PROVINCE_ID "+provTxt+" ");
							sb.append("AND TRANSACTION_TIMESTAMP = " + this.dateUtil().setTimeStamp2DB(bufferMap.get("TRANSACTION_TIMESTAMP").toString()) + " ");
						}
						Map map = dao.findDataSql(sb.toString());

						result.put("code", allocateMap.get("ALLOCATE_CODE").toString());
						result.put("name", allocateMap.get("ALLOCATE_NAME").toString());
						result.put("level", allocateMap.get("ALLOCATE_LEVEL").toString());
						result.put("I", bufferMap.get("I"));
						result.put("X", bufferMap.get("X"));
						result.put("R", "0");
						result.put("date", bufferMap.get("DATES"));
						result.put("refNo", bufferMap.get("REF_NO"));
						result.put("docNo", map.get("DOC_NO"));
						result.put("desc", map.get("DESCS"));
						listData.add(result);
					}
				}
				
			}

			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\""+budgetYear +".xls\"");
			response.setCharacterEncoding("windows-874");
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("sheet 0");
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFRow row = sheet.createRow((short)0);
			HSSFCell cell1 = row.createCell(0);
			HSSFCell cell2 = row.createCell(1);
			HSSFCell cell3 = row.createCell(2);
			HSSFCell cell4 = row.createCell(3);
			HSSFCell cell5 = row.createCell(4);
			HSSFCell cell6 = row.createCell(5);
			HSSFCell cell7 = row.createCell(6);
			HSSFCell cell8 = row.createCell(7);
			HSSFCell cell9 = row.createCell(8);
			
			sheet.setDefaultColumnWidth((short) 25);
			cell1.setCellStyle(cellStyle);
			cell2.setCellStyle(cellStyle);
			cell3.setCellStyle(cellStyle);
			cell4.setCellStyle(cellStyle);
			cell5.setCellStyle(cellStyle);
			cell6.setCellStyle(cellStyle);
			cell7.setCellStyle(cellStyle);
			cell8.setCellStyle(cellStyle);
			cell9.setCellStyle(cellStyle);
			
			cell1.setCellValue("วันที่");
			cell2.setCellValue("เลขที่เอกสาร");
			cell3.setCellValue("เลขที่อ้างอิง");
			cell4.setCellValue("รหัสโอนเงินคู่สัญญา");
			cell5.setCellValue("ชื่อกองทุน");
			cell6.setCellValue("รายการ");
			cell7.setCellValue("รายรับ");
			cell8.setCellValue("รายจ่าย");
			cell9.setCellValue("คงเหลือ");
			
			if (listData.size() > 0) {
				HSSFCellStyle cellStyleAlignRigth = wb.createCellStyle();
				HSSFCellStyle cellStyleAlignLeft = wb.createCellStyle();
				HSSFCellStyle cellStyleAlignCenter = wb.createCellStyle();
				cellStyleAlignRigth.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cellStyleAlignLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);
				cellStyleAlignCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				
				double sum = 0;
				double bufferSumIncomeAmount = 0;
				double bufferSumExpenseAmount = 0;
				String date = "";
				boolean isSum = true;
				Map map = new HashMap();
				for(int i=0;i<listData.size();i++){
					isSum = true;
					map = (Map)listData.get(i);
					row = sheet.createRow((short)(i+1));
					cell1 = row.createCell(0);
					cell2 = row.createCell(1);
					cell3 = row.createCell(2);
					cell4 = row.createCell(3);
					cell5 = row.createCell(4);
					cell6 = row.createCell(5);
					cell7 = row.createCell(6);
					cell8 = row.createCell(7);
					cell9 = row.createCell(8);
					
					cell7.setCellStyle(cellStyleAlignRigth);
					cell8.setCellStyle(cellStyleAlignRigth);
					cell9.setCellStyle(cellStyleAlignRigth);
					date = this.dateUtil().convertDate("DP", map.get("date").toString());
					cell1.setCellValue(date);
					cell2.setCellValue(dao.chkData(map.get("docNo")));
					cell3.setCellValue(dao.chkData(map.get("refNo")));
					cell4.setCellValue(dao.chkData(map.get("code")));
					cell5.setCellValue(dao.chkData(map.get("name")));
					cell6.setCellValue(dao.chkData(map.get("desc")));

					if(map.get("I")!= null && !map.get("I").toString().equals("")){
						cell7.setCellValue(this.digitUtil().numberFormat("#,##0.00", map.get("I").toString()));
						bufferSumIncomeAmount = Double.parseDouble(map.get("I").toString());
					}else{
						cell7.setCellValue("0.00");
						bufferSumIncomeAmount = 0;
					}
					if(map.get("X") != null && !map.get("X").toString().equals("")){
						cell8.setCellValue(this.digitUtil().numberFormat("#,##0.00", map.get("X").toString()));
						bufferSumExpenseAmount = Double.parseDouble(map.get("X").toString());
					}else{
						cell8.setCellValue("0.00");
						bufferSumExpenseAmount = 0;
					}
					if(map.get("R") != null && !map.get("R").toString().equals("0")){
						cell9.setCellValue(this.digitUtil().numberFormat("#,##0.00", map.get("R").toString()));
						sum = 0;
					}else{
						if (map.get("X").toString().equals("0") && map.get("I").toString().equals("0")) {
							isSum = false;
						}
						
						if (isSum) {
							sum = sum + (bufferSumIncomeAmount - bufferSumExpenseAmount);
							cell9.setCellValue(this.digitUtil().formatDecimal(sum, 2));
						} else if (!isSum) {
							cell9.setCellValue(this.digitUtil().formatDecimal(0, 2));
						}
					}
					
				}
			}

			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
